package gwtappcontainer.server.apps.insight;
import gwtappcontainer.server.Utils;
import gwtappcontainer.server.apps.APIException;
import gwtappcontainer.shared.apis.APIResponse.Status;
import gwtappcontainer.shared.apps.insight.Teacher;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
public class TeacherRepository {
public static Teacher get(String email) {
try {
String sql = "select teacher_id, first_name, last_name, email from teachers where email = ?";
Teacher teacher = null;
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, email.toLowerCase());
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
teacher = new Teacher();
teacher.id = resultSet.getInt(1);
teacher.firstName = resultSet.getString(2);
teacher.lastName = resultSet.getString(3);
teacher.email = resultSet.getString(4);
}
}
}
}
return teacher;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static void add(String email, String firstName, String lastName) {
if (null != get(email))
throw new APIException(Status.ERROR_RESOURCE_ALREADY_EXISTS,
"[" + email + "] is already present");
try {
String sql = "insert into teachers values (0, ?, ?, ?)";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, email.toLowerCase());
ps.setString(2, firstName.toLowerCase());
ps.setString(3, lastName.toLowerCase());
ps.executeUpdate();
}
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static ArrayList<Teacher> getAll() {
try {
String sql = "select teacher_id, email, first_name, last_name from teachers order by first_name, last_name";
ArrayList<Teacher> teachers = new ArrayList<>();
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
Teacher teacher = new Teacher();
teacher.id = resultSet.getInt(1);
teacher.email = resultSet.getString(2);
teacher.firstName = resultSet.getString(3);
teacher.lastName = resultSet.getString(4);
teachers.add(teacher);
}
}
}
}
return teachers;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
}